package com.anjie.powerproject.repository;

import com.anjie.powerproject.entity.DeviceValue;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

/**
 * 读取点位数据
 */
public interface DeviceValueRepository extends JpaRepository<DeviceValue, Long> {

    /**
     * 根据设备code读取历史实际点位数据
     */
    @Query(nativeQuery = true, value = "select dev_code, DATE_FORMAT(`sdate`, '%Y-%m-%d') as `sdate`, SUM(`data`) as `data` from (select @i \\:= 0) r, data_rt where dev_code = :code and `sdate` >= :start and `sdate` < :end group by DATE_FORMAT(`sdate`, '%Y-%m-%d')")
    List<DeviceValue> getDeviceValues(@Param("code") String deviceCode, @Param("start") String startTime, @Param("end") String endTime);

    /**
     * 读取某时间点的点位数据
     */
    @Query(nativeQuery = true, value = "select dev_code, DATE_FORMAT(`sdate`, '%Y-%m-%d %H') as `sdate`, `data` from data_rt where dev_code = :code and DATE_FORMAT(`sdate`, '%Y-%m-%d') = :time limit 1")
    DeviceValue getDeviceValueByTime(@Param("code") String deviceCode, @Param("time") String time);

    /**
     * 读取某时间点的点位数据(使用devId)
     */
    @Query(nativeQuery = true, value = "select A.dev_code, DATE_FORMAT(`sdate`, '%Y-%m-%d %H') as `sdate`, `data` from data_rt A join device B on A.dev_code = B.dev_code where B.device_id = :devId and DATE_FORMAT(`sdate`, '%Y-%m-%d') = :time limit 1")
    DeviceValue getDeviceValueByTime(@Param("devId") Integer devId, @Param("time") String time);
}
